package org.iteam.core.service.oracle.impl.sys;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.iteam.core.Constant.Function;
import org.iteam.core.Constant.Permission;
import org.iteam.core.Constant.Status;
import org.iteam.core.model.sys.SysPermission;
import org.iteam.core.model.sys.SysRole;
import org.iteam.core.service.DBService;
import org.iteam.core.service.sys.SysRoleService;
import org.iteam.core.utils.DateUtil;
import org.iteam.core.utils.SqlBuilder;
import org.iteam.core.view.TreeGridNode;
import org.iteam.core.view.sys.PermissionView;

public class SysRoleServiceImpl extends SysRoleService {
	@Override
	public boolean create(SysRole obj) throws SQLException {
		SqlBuilder sb = SqlBuilder.createInsert(SysRole.TABLE_NAME);
		sb.addField("code", obj.getCode());
		sb.addField("name", obj.getName());
		sb.addField("alias", obj.getAlias());
		sb.addField("status", obj.getStatus());
		sb.addField("create_time", DateUtil.currentTime());
		String sql = " INSERT INTO SYS_ROLE (CODE,NAME,ALIAS,STATUS,CREATE_TIME)  VALUES(?,?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss'))";
		return super.update(sql, sb.getParams());
	}

	@Override
	public List<SysRole> queryByPage(long page, long rows) throws SQLException {
		return super.query(
				DBService.toOraclePage(
						"select * from " + SysRole.TABLE_NAME
								+ " order by code ,create_time desc")
						.toUpperCase(), page * rows, rows * (page - 1));
	}

	public long countSub(String role) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1)  FROM SYS_FUNCTION A ");
		sql.append("LEFT JOIN ( SELECT * FROM SYS_PERMISSION C WHERE C.PERMISSION_ROLE = ? ) B ");
		sql.append("ON B.PERMISSION_FUNCTION = A.CODE ");
		sql.append("WHERE A.STATUS = ?  AND A.TYPE IN ( ?,?) AND A.\"MODE\" = ?   ");
		return Long.parseLong(super.certain(sql.toString().toUpperCase(), role,
				Status.ENABLE.ecode, Function.Type.MODULE.ecode,
				Function.Type.FUNCTION.ecode, Function.Mode.AUTH.ecode)
				.toString());
	}

	public boolean updateRoleFunctions(String role, String _module,
			boolean _permission, int _type) throws SQLException {
		Permission.Mode pMode = null;
		switch (_type) {
		case 0:
			break;
		// 允许
		case 1:
			pMode = Permission.Mode.PERMIT;
			break;
		// 禁止
		case 2:
			pMode = Permission.Mode.PROHIBIT;
			break;
		default:
			pMode = Permission.Mode.PERMIT;
			break;
		}
		SqlBuilder sb = null;
		String sql = "";
		if (_permission) {
			sb = SqlBuilder.createInsert(SysPermission.TABLE_NAME);
			sb.addField("permission_function", _module);
			sb.addField("permission_role", role);
			sb.addField("mode", pMode.ecode);
			sb.addField("beg_time", DateUtil.currentTime());
			sb.addField("end_time", DateUtil.currentTime());
			sql = "INSERT INTO SYS_PERMISSION  (PERMISSION_FUNCTION,PERMISSION_ROLE,\"MODE\",BEG_TIME,END_TIME) VALUES(?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss'),to_date(?,'yyyy-mm-dd hh24:mi:ss'))";

		} else {
			sb = SqlBuilder.createDelete(SysPermission.TABLE_NAME);
			sb.addWhere("permission_role", role);
			sb.addWhere("permission_function", _module);
			sql = sb.getSql();
		}
		return super.update(sql.toUpperCase(), sb.getParams());
	}

	@Override
	public List<PermissionView> querySub(long page, long rows, String role)
			throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT B.\"MODE\" AS M_MODE,B.\"PERMISSION_ROLE\",A.\"CODE\",A.\"PARENT\",A.\"NAME\",A.\"AUTH\",A.\"MODE\" ,(SELECT \"NAME\" FROM SYS_FUNCTION D WHERE D.\"CODE\" = A.\"PARENT\" ) AS PARENTNAME FROM SYS_FUNCTION A ");
		sql.append("LEFT JOIN ( SELECT * FROM SYS_PERMISSION C WHERE C.PERMISSION_ROLE = ? ) B ");
		sql.append("ON B.PERMISSION_FUNCTION = A.CODE ");
		sql.append("WHERE A.STATUS = ?  AND A.TYPE IN ( ?,?) AND A.\"MODE\" = ?  ORDER BY A.\"PARENT\",A.\"CODE\"");
		return super.query(PermissionView.class,
				DBService.toOraclePage(sql.toString()), role,
				Status.ENABLE.ecode, Function.Type.MODULE.ecode,
				Function.Type.FUNCTION.ecode, Function.Mode.AUTH.ecode, page
						* rows, rows * (page - 1));
	}

	public List<TreeGridNode> getGridNodes(String role) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ( ");
		sb.append("select A_1.*,(SELECT count(1) from SYS_FUNCTION B_1 WHERE B_1.PARENT = A_1.CODE AND B_1.TYPE = 'MENU' AND B_1.STATUS = 'ENABLE')  MENU_COUNT,  ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_2 WHERE B_2.PARENT = A_1.CODE AND B_2.TYPE = 'MODULE' AND B_2.\"MODE\" = 'AUTH'  AND B_2.STATUS = 'ENABLE')  MODULE_COUNT ,  ");
		sb.append("		(SELECT count(1) from SYS_FUNCTION B_3 WHERE B_3.PARENT = A_1.CODE AND B_3.TYPE = 'FUNCTION' AND B_3.\"MODE\" = 'AUTH' AND B_3.STATUS = 'ENABLE')  FUNCTION_COUNT  ");
		sb.append(" from SYS_FUNCTION  A_1 WHERE  A_1.TYPE = 'MENU'  AND A_1.STATUS = 'ENABLE' AND A_1.PARENT IS NULL)  TEMP_TABLE_1 ORDER BY TEMP_TABLE_1.SORT DESC ");
		List<TreeGridNode> list = new ArrayList<TreeGridNode>();
		try {
			list = super.query(TreeGridNode.class, sb.toString());
			for (TreeGridNode treeGridNode : list) {
				treeGridNode.setState("open");
				if (treeGridNode.getFunction_count() > 0) {
					treeGridNode.addChildren(getFunctions(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getMenu_count() > 0) {
					treeGridNode.addChildren(getMenu(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getModule_count() > 0) {
					treeGridNode.addChildren(getModules(role,
							treeGridNode.getCode()));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private List<TreeGridNode> getMenu(String role, String parent) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ( ");
		sb.append("select A_1.*,(SELECT count(1) from SYS_FUNCTION B_1 WHERE B_1.PARENT = A_1.CODE AND B_1.TYPE = 'MENU' AND B_1.STATUS = 'ENABLE')  MENU_COUNT,  ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_2 WHERE B_2.PARENT = A_1.CODE AND B_2.TYPE = 'MODULE' AND B_2.\"MODE\" = 'AUTH'  AND B_2.STATUS = 'ENABLE')  MODULE_COUNT ,  ");
		sb.append("		(SELECT count(1) from SYS_FUNCTION B_3 WHERE B_3.PARENT = A_1.CODE AND B_3.TYPE = 'FUNCTION' AND B_3.\"MODE\"  = 'AUTH' AND B_3.STATUS = 'ENABLE')  FUNCTION_COUNT  ");
		sb.append(" from SYS_FUNCTION  A_1 WHERE  A_1.TYPE = 'MENU'  AND A_1.STATUS = 'ENABLE' AND A_1.PARENT = ? )  TEMP_TABLE_1 ORDER BY TEMP_TABLE_1.SORT DESC ");
		
		List<TreeGridNode> list = new ArrayList<TreeGridNode>();
		try {
			list = super.query(TreeGridNode.class, sb.toString(), parent);
			for (TreeGridNode treeGridNode : list) {
				if (treeGridNode.getFunction_count() > 0) {
					treeGridNode.addChildren(getFunctions(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getMenu_count() > 0) {
					treeGridNode.addChildren(getMenu(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getModule_count() > 0) {
					treeGridNode.addChildren(getModules(role,
							treeGridNode.getCode()));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private List<TreeGridNode> getModules(String role, String parent) {
		StringBuffer sb = new StringBuffer();
		sb.append("select TEMP_TABLE_2.\"MODE\" AS M_MODE, TEMP_TABLE_2.PERMISSION_ROLE, TEMP_TABLE_1.* from (SELECT * 	FROM SYS_PERMISSION C WHERE C.PERMISSION_ROLE = ?) TEMP_TABLE_2 RIGHT  JOIN (     ");
		sb.append("select A_1.*,(SELECT count(1) from SYS_FUNCTION B_1 WHERE B_1.PARENT = A_1.CODE AND B_1.TYPE = 'MENU' AND B_1.STATUS = 'ENABLE')  MENU_COUNT,     ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_2 WHERE B_2.PARENT = A_1.CODE AND B_2.TYPE = 'MODULE'  AND B_2.\"MODE\" = 'AUTH' AND B_2.STATUS = 'ENABLE')  MODULE_COUNT,     ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_3 WHERE B_3.PARENT = A_1.CODE AND B_3.TYPE = 'FUNCTION' AND B_3.\"MODE\" = 'AUTH'  AND B_3.STATUS = 'ENABLE')  FUNCTION_COUNT      ");
		sb.append(" from SYS_FUNCTION  A_1 WHERE  A_1.TYPE = 'MODULE'  AND A_1.STATUS = 'ENABLE'  AND A_1.\"MODE\" = 'AUTH' AND A_1.PARENT = ? )  TEMP_TABLE_1 ON TEMP_TABLE_2.PERMISSION_FUNCTION = TEMP_TABLE_1.CODE  ORDER BY TEMP_TABLE_1.SORT DESC     ");

		List<TreeGridNode> list = new ArrayList<TreeGridNode>();
		try {
			list = super.query(TreeGridNode.class, sb.toString(), role, parent);
			for (TreeGridNode treeGridNode : list) {
				if (treeGridNode.getFunction_count() > 0) {
					treeGridNode.addChildren(getFunctions(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getMenu_count() > 0) {
					treeGridNode.addChildren(getMenu(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getModule_count() > 0) {
					treeGridNode.addChildren(getModules(role,
							treeGridNode.getCode()));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private List<TreeGridNode> getFunctions(String role, String parent) {
		StringBuffer sb = new StringBuffer();
		sb.append("select TEMP_TABLE_2.\"MODE\" AS M_MODE, TEMP_TABLE_2.PERMISSION_ROLE, TEMP_TABLE_1.* from (SELECT * 	FROM SYS_PERMISSION C WHERE C.PERMISSION_ROLE = ?) TEMP_TABLE_2 RIGHT  JOIN (     ");
		sb.append("select A_1.*,(SELECT count(1) from SYS_FUNCTION B_1 WHERE B_1.PARENT = A_1.CODE AND B_1.TYPE = 'MENU' AND B_1.STATUS = 'ENABLE')  MENU_COUNT,     ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_2 WHERE B_2.PARENT = A_1.CODE AND B_2.TYPE = 'MODULE'  AND B_2.\"MODE\" = 'AUTH' AND B_2.STATUS = 'ENABLE')  MODULE_COUNT,     ");
		sb.append("(SELECT count(1) from SYS_FUNCTION B_3 WHERE B_3.PARENT = A_1.CODE AND B_3.TYPE = 'FUNCTION' AND B_3.\"MODE\" = 'AUTH'  AND B_3.STATUS = 'ENABLE')  FUNCTION_COUNT      ");
		sb.append(" from SYS_FUNCTION  A_1 WHERE  A_1.TYPE = 'FUNCTION'  AND A_1.STATUS = 'ENABLE'  AND A_1.\"MODE\" = 'AUTH' AND A_1.PARENT = ? )  TEMP_TABLE_1 ON TEMP_TABLE_2.PERMISSION_FUNCTION = TEMP_TABLE_1.CODE  ORDER BY TEMP_TABLE_1.SORT DESC     ");

		List<TreeGridNode> list = new ArrayList<TreeGridNode>();
		try {
			list = super.query(TreeGridNode.class, sb.toString(), role, parent);
			for (TreeGridNode treeGridNode : list) {
				if (treeGridNode.getFunction_count() > 0) {
					treeGridNode.addChildren(getFunctions(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getMenu_count() > 0) {
					treeGridNode.addChildren(getMenu(role,
							treeGridNode.getCode()));
				}
				if (treeGridNode.getModule_count() > 0) {
					treeGridNode.addChildren(getModules(role,
							treeGridNode.getCode()));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}
